from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref

# 1. 生成模型基类
Base = declarative_base()


# 2. 创建数据表对应的类
class User(Base):
    # 对应MySQL中数据表的名字
    __tablename__ = 'users'

    # 创建字段
    id = Column(Integer, primary_key=True)  # users表中的id字段(主键)
    username = Column(String(64), nullable=False)  # users表中的username字段
    password = Column(String(64), nullable=False)  # users表中的password字段
    email = Column(String(64), nullable=False, index=True)  # users表中的email字段(有索引)
    address = Column(String(200), nullable=True)  # users表中的address字段

    def __repr__(self):
        return '%s(%r)' % (self.__class__.__name__, self.username)


class Teacher(Base):
    # 表名
    __tablename__ = 'teacher'
    # 主键 id
    id = Column(Integer, primary_key=True, autoincrement=True)
    # 用户名　　字符串　　不能为空
    name = Column(String(20), nullable=False)


class Course(Base):
    # 表名
    __tablename__ = 'course'

    # 主键 id
    id = Column(Integer, primary_key=True, autoincrement=True)
    # 用户名　　字符串　　不能为空　创建索引
    name = Column(String(20), nullable=False)
    teacher_id = Column(Integer, ForeignKey('teacher.id'), nullable=False, unique=True)
    teacher = relationship('Teacher', backref=backref('course', uselist=False))


class Book(Base):
    __tablename__ = 'book'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(30), nullable=False)
    # 可以通过book表查hero,数据库中不会创建heros字段,只是建立联系
    heros = relationship('Hero', backref='book')


class Hero(Base):
    __tablename__ = 'hero'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(30), nullable=False)
    age = Column(Integer, default=0)
    book_id = Column(Integer, ForeignKey('book.id'))


# 学生表
class Student(Base):
    __tablename__ = 'student'
    sid = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
    name = Column(String(30), nullable=False)
    age = Column(Integer, nullable=False)
    # 声明学生和课程表的关系和关系表的名字
    courses = relationship('CourseNew', backref='stus', secondary='stu_course')


# 课程表
class CourseNew(Base):
    __tablename__ = 'course_new'
    cid = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
    name = Column(String(30), nullable=False)


# 学生课程的关系表
class StuCourse(Base):
    __tablename__ = 'stu_course'
    nid = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
    # 学生id外键
    stu_id = Column(Integer, ForeignKey('student.sid'))
    # 课程id外键
    course_id = Column(Integer, ForeignKey('course_new.cid'))
